# -*- coding: utf-8 -*-
# @Time : 2022/7/6 17:39
# @Author : renyumeng
# @Email : 2035328756@qq.com
# @File : runMYSQL.py
# @Project : DataStructureManagementSystem
from utils.SQL.config import host, user, password, dataBaseName
import logging
import pymysql
import time
from typing import Tuple, Union, Any
from pymysql.cursors import DictCursor, Cursor
from pymysql.constants import CLIENT


class SQLOperation:
    def __init__(self, db_host=host, db_user=user, db_password=password,
                 db_name=dataBaseName, cursor_class=Cursor):
        self.host = db_host
        self.user = db_user
        self.pwd = db_password
        self.name = db_name
        self.cursor_class = cursor_class

    def run_sql(self, sql, operation="SELECT", if_CLIENT=False, if_get_id=False):
        """
        :param if_get_id: 是否得到上一次增删改的id
        :param if_CLIENT: 是否多行执行sql
        :param operation: sql操作
        :param sql: 执行的sql语句
        :return: 查询结果
        """
        con = None
        while True:
            try:
                if if_CLIENT:
                    con = pymysql.connect(host=self.host, user=self.user, password=self.pwd,
                                          database=self.name, cursorclass=self.cursor_class,
                                          client_flag=CLIENT.MULTI_STATEMENTS)
                else:
                    con = pymysql.connect(host=self.host, user=self.user, password=self.pwd,
                                          database=self.name, cursorclass=self.cursor_class)
                break
            except:
                logging.error('Cannot connect to database,trying again')
                time.sleep(1)
        cur = con.cursor()
        try:
            if type(sql) == str:
                cur.execute(sql)
            elif type(sql) == list:
                for i in sql:
                    cur.execute(i)
        except pymysql.OperationalError as e:
            logging.error(e)
            cur.close()
            con.close()
            return False
        con.commit()
        data = cur.fetchall()
        cur.close()
        con.close()

        if operation == "SELECT":
            return data
        elif operation == "INSERT" or operation == "UPDATE":
            if if_get_id:
                return {
                    'status': True,
                    'id': cur.lastrowid
                }
            return True

    def deal_sql_result(self, sql: str, *key: str):
        """
        :param sql: 执行的sql语句
        :param key: 查询数据的键值
        :return:查询的数据
        """
        query_result: Union[bool, Tuple[Tuple[Any, ...], ...]] = self.run_sql(sql)
        result_lst = []
        if len(query_result) == 0:
            return {}
        for i in range(len(query_result)):
            dict_res = {}
            for j in range(len(query_result[i])):
                dict_res[key[j]] = query_result[i][j]
            result_lst.append(dict_res)
        return result_lst

    @staticmethod
    def load_sql(path):
        """
        :param path: sql文件的地址必须是绝对路径
        :return: Sql语句
        """
        sql = ""
        try:
            with open(path) as file:
                sql_list = file.readlines()
                for i in range(len(sql_list)):
                    sql += sql_list[i]
            file.close()
        except:
            logging.error("can not find this file.")
            return None
        return sql


if __name__ == "__main__":
    SQL = """
    select result.stu_name,result.stu_id,result.teacher_name,tc.class_name
from (select t1.stu_name, t1.stu_id, t1.Class_id, t2.teacher_name
      from Students_student t1,
           Teachers_teacher t2
      where t1.teacher_id = t2.id) result,
     Teachers_classes tc
where result.Class_id = tc.id;
    """
    print(SQLOperation().deal_sql_result(SQL,
                                         "学生姓名",
                                         "学号",
                                         "对应教师",
                                         "班级"))
